package org.dydl.dao.system.repository;

import java.util.List;

import org.dydl.model.system.Goods;
import org.dydl.model.system.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface IGoodsRepository extends CrudRepository<Product, Integer>,JpaSpecificationExecutor<Product>{
	
	
	

	@Query(value = " FROM Goods go ", countQuery = "SELECT count(go.id) FROM Goods go")
	 Page<Goods> getGoodsList(Pageable pageable);
	
	@Query(value = " FROM Goods go WHERE go.name LIKE :name ", countQuery = "SELECT count(go.id) FROM Goods go WHERE go.name LIKE :name ")
	 Page<Goods> getGoodsListLike(@Param("name") String name, Pageable pageable);
	
	@Query(value = "SELECT * FROM goods WHERE SKU LIKE %:sku%" ,nativeQuery = true)
	 List<Goods> get(@Param("sku") String sku);
	
	@Query(value = "SELECT * FROM product WHERE name LIKE %:name% " , nativeQuery = true)
	 List<Product> findByName(@Param("name")String name);

	@Query(value = "SELECT * FROM goods WHERE TYPE =:type AND SKU LIKE %:sku%  " ,nativeQuery = true)
	List<Goods> getGoodsBySku(@Param("sku")String sku,@Param("type") String type);

	@Query(value = "SELECT * FROM goods WHERE TYPE !='CP' AND NAME LIKE %:name%  " ,nativeQuery = true)
	List<Goods> getMaterialByName(@Param("name")String name);
	
	@Query(value = "SELECT * FROM product WHERE NAME LIKE %:name%" ,nativeQuery = true)
	List<Product> getGoodsByName(@Param("name") String name);

	@Modifying
	@Query(value = "UPDATE goods  set IN_STOCK_QTY =:stockQty + IN_STOCK_QTY   where UUID =:uuid", nativeQuery = true)
	int updateGoodsStockQtyByGoodsUUID (@Param("stockQty") int stockQty,@Param("uuid") String uuid);
	
	@Query(value = "SELECT IN_STOCK_QTY FROM goods WHERE UUID =:uuid",nativeQuery = true)
	Integer findInStockQTYByUUID(@Param("uuid") String uuid);
	
	@Modifying
	@Query(value = "UPDATE goods SET IN_STOCK_QTY =IN_STOCK_QTY - :stockQty,CONSUMPTION = CONSUMPTION + :stockQty where UUID =:uuid",nativeQuery = true)
	void updateStowageNo(@Param("stockQty") int stockQty,@Param("uuid") String uuid);
}
